import path from "path"
import * as fs from 'fs';
import client from "./db.mjs";
import Profile from "./profile.mjs";
//导入Exercises.json文件
//import ExercisesJson from `./Exercises.json` assert { type: `json` };
const ExercisesJson = JSON.parse(fs.readFileSync('./Exercises.json', 'utf8'));
const PilatesJson = JSON.parse(fs.readFileSync('./Pilates.json', 'utf8'));
const exerciseToEquipmentsJson = JSON.parse(fs.readFileSync('./exerciseToEquipments.json', 'utf8'));
const YogaJson = JSON.parse(fs.readFileSync('./Yoga.json', 'utf8'));
const MobilityJson = JSON.parse(fs.readFileSync('./Mobility.json', 'utf8'));
//新建一个类，做数据的入库、更新
class ParseData {
    exerciseTypes = {}
    exerciseTypesZhCN = {}
    equipment = {}
    equipmentZhCN = {}
    activity = {}
    activityZhCN = {}
    constructor() {
        this.exerciseTypes = this.readFile('./exercise_types.properties');
        this.exerciseTypesZhCN = this.readFile('./exercise_types_zh_CN.properties');
        this.equipment = this.readFile('./exercise_equipments.properties');
        this.equipmentZhCN = this.readFile('./exercise_equipments_zh_CN.properties');
        this.activity = this.readFile('./activity_types.properties');
        this.activityZhCN = this.readFile('./activity_types_zh_CN.properties');
        console.log("解析文件properties完成");

        // console.log("Profile:", Profile);
        //  debugger
    }

    //读取本地配置文件
    readFile(filePath) {
        let types = {};
        let data = fs.readFileSync(filePath, 'utf-8');
        let lines = data.split('\n');
        for (let line of lines) {
            //忽略空白行
            if (line.trim() === '') {
                continue;
            }
            let arr = line.split('=');
            let key = arr[0];
            let value = arr[1];
            types[key] = value;
        }
        return types;
    }
    //判断某个表是否有数据
    async isExistData(tableName) {
        let sql = `SELECT count(*) FROM ${tableName}`;
        let result = await client.query(sql);
        if (result.rowCount > 0) {
            return true;
        } else {
            return false;
        }
    }
    //读取当前目录下的文件
    readFileList(dir = '', filesList = []) {
        const files = fs.readdirSync(dir);
        files.forEach((item, index) => {
            var fileArr = item.split('.');
            var filePath = path.join(dir, item);
            var stat = fs.statSync(filePath);
            if (stat.isDirectory()) {
                readFileList(path.join(dir, item), filesList);
            }
        })
    }


    parseExerciseCategoryData() {
        // CREATE TABLE sys_exercise_category(
        //     id SMALLINT PRIMARY KEY,
        //     key TEXT, --ExerciseCategoryBenchPress
        //     name TEXT,
        //     en_name TEXT,
        //     cn_name TEXT
        // );
        //
        // let sql = `INSERT INTO sys_exercise_category(id,key,en_name,cn_name) VALUES($1,$2,$3,$4)`;
        //sys_exercise_category数据来自 Profile 中的 exerciseCategory，
        let exerciseCategory = Profile.types.exerciseCategory;

        //exerciseCategory: {
        //     0: "benchPress",
        //         1: "calfRaise",
        // }
        //exerciseCategory对象如上
        //id 是 exerciseCategory 对象的字段名 0
        //key 是 exerciseCategory 字段对应的 value，转成下划线类型的，字母大写，例如 benchPress -> BENCH_PRESS
        //cn_name 从 `exercise_types_zh_CN.properties` 文件中，找到以 `category_type_KEY=` 开头的行获取中文名 (例如 `category_type_BENCH_PRESS=卧推`)
        //en_name 从`exercise_types.properties` (英文版) 文件中获取。
        //console.log("sql：", sql);
        //  console.log("exerciseCategory ", exerciseCategory);
        let exerciseCategoryData = [];
        for (let id in exerciseCategory) {
            //id大于1000不处理
            if (id > 1000) {
                continue;
            }
            let key = exerciseCategory[id];
            //将key转成大写，并加下划线，例如 benchPress -> BENCH_PRESS
            key = key.replace(/[A-Z]/g, (item) => {
                return '_' + item;
            }).toUpperCase();
            //读取本地文件文件exercise_types_zh_CN.properties中的内容
            //  console.log("key：", key);
            //从exercise_types_zh_CN.properties文件中获取
            let cn_name = this.exerciseTypesZhCN[`category_type_${key}`];
            let en_name = this.exerciseTypes[`category_type_${key}`];
            let values = [parseInt(id), key, en_name, cn_name];
            //console.log("入库数据：", values);
            //await client.query(sql, values);
            exerciseCategoryData.push(values);
        }
        return exerciseCategoryData;
    }

    async updateExerciseCategoryData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_exercise_category(id,key,en_name,cn_name) VALUES($1,$2,$3,$4)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }

        let updateData = compareData.updateData;
        //更新数据sql
        sql = `UPDATE sys_exercise_category SET key=$2,en_name=$3,cn_name=$4 WHERE id=$1`;
        for (let i = 0; i < updateData.length; i++) {
            let values = updateData[i];
            await client.query(sql, values);
        }
    }

    async updateMuscleData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_muscles(key,en_name,cn_name) VALUES($1,$2,$3)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }

        let updateData = compareData.updateData;
        //更新数据sql
        sql = `UPDATE sys_muscles SET  en_name=$2,cn_name=$3 WHERE key=$1`;
        for (let i = 0; i < updateData.length; i++) {
            let values = updateData[i];
            await client.query(sql, values);
        }
    }

    //对比数据
    async compareExerciseCategoryData(dataList) {
        let newData = [];
        let updateData = [];
        //无改动数据
        let noChangeData = [];
        let sql = `SELECT id,key,en_name,cn_name FROM sys_exercise_category where id=$1`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, [values[0]]);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
                continue;
            }
            if (dbData[0].key != values[1] || dbData[0].en_name != values[2] || dbData[0].cn_name != values[3]) {
                updateData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, updateData, noChangeData };
    }

    parseMusclesData() {
        //在exerciseTypes中找到所有muscle_type_开头的数据
        let enkeys = Object.keys(this.exerciseTypes).filter(item => { return item.startsWith('muscle_type_'); });
        let zhkeys = Object.keys(this.exerciseTypesZhCN).filter(item => { return item.startsWith('muscle_type_'); });
        // key en_name cn_name
        //合并两个keys，不重复
        let keys = [...new Set([...enkeys, ...zhkeys])];

        let musclesData = [];
        for (let i = 0; i < keys.length; i++) {
            let key = keys[i];
            let en_name = this.exerciseTypes[key];
            let cn_name = this.exerciseTypesZhCN[key];
            //key去掉muscle_type_
            key = key.replace('muscle_type_', '');
            let values = [key, en_name, cn_name];
            musclesData.push(values);
        }
        return musclesData;
    }

    //对比数据
    async compareMuscleData(dataList) {
        let newData = [];
        let updateData = [];
        //无改动数据
        let noChangeData = [];
        let sql = `SELECT key,en_name,cn_name FROM sys_muscles where key=$1`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, [values[0]]);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
                continue;
            }
            if (dbData[0].key != values[0] || dbData[0].en_name != values[1] || dbData[0].cn_name != values[2]) {
                updateData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, updateData, noChangeData };
    }

    //解析设备数据
    parseEquipmentData() {
        //在exerciseTypes中找到所有exercise_equipment_开头的数据
        let enkeys = Object.keys(this.equipment).filter(item => { return item.startsWith('exercise_equipment_'); });
        let zhkeys = Object.keys(this.equipmentZhCN).filter(item => { return item.startsWith('exercise_equipment_'); });
        // key en_name cn_name
        //合并两个keys，不重复
        let keys = [...new Set([...enkeys, ...zhkeys])];

        let equipmentData = [];
        for (let i = 0; i < keys.length; i++) {
            let key = keys[i];
            let en_name = this.equipment[key];
            let cn_name = this.equipmentZhCN[key];
            //key去掉exercise_equipment_
            key = key.replace('exercise_equipment_', '');
            let values = [key, en_name, cn_name];
            equipmentData.push(values);
        }
        return equipmentData;
        // key en_name cn_name
    }

    //对比数据
    async compareEquipmentData(dataList) {
        let newData = [];
        let updateData = [];
        //无改动数据
        let noChangeData = [];
        let sql = `SELECT key,en_name,cn_name FROM sys_equipment where key=$1`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, [values[0]]);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
                continue;
            }
            if (dbData[0].key != values[0] || dbData[0].en_name != values[1] || dbData[0].cn_name != values[2]) {
                updateData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, updateData, noChangeData };
    }

    async updateEquipmentData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_equipment(key,en_name,cn_name) VALUES($1,$2,$3)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }

        let updateData = compareData.updateData;
        //更新数据sql
        sql = `UPDATE sys_equipment SET  en_name=$2,cn_name=$3 WHERE key=$1`;
        for (let i = 0; i < updateData.length; i++) {
            let values = updateData[i];
            await client.query(sql, values);
        }
    }

    toSnakeCase(str) {
        const result = [];
        for (let i = 0; i < str.length; i++) {
            const char = str[i];
            // 如果是大写字母
            if (/[A-Z]/.test(char)) {
                // 如果不是第一个字符，添加下划线
                if (i > 0) {
                    result.push('_');
                }
                // 添加小写形式
                result.push(char);
            } else if (/[0-9]/.test(char)) {
                //  数字直接添加
                if (i > 0) {
                    let preChar = str[i - 1]
                    //前一个字母不是下划线或者数字，添加下划线
                    if (!/[_0-9]/.test(preChar)) {
                        result.push('_');
                    }
                } else {
                    //第一位，需要直接加下划线
                    result.push('_');
                }
                result.push(char);
            } else {
                // 小写字母 
                result.push(char.toUpperCase());
            }
        }

        return result.join('');

        // return str.replace(/[0-9]+|[A-Z]/g, (item) => {
        //     return '_' + item;
        // }).toUpperCase();
    }
    parseExerciseData() {
        //通过exerciseCategory找对应数据
        let data = []
        let exerciseCategory = Profile.types.exerciseCategory
        for (let category_id in exerciseCategory) {
            //id大于1000不处理
            if (category_id > 1000) {
                continue;
            }
            let key = exerciseCategory[category_id];
            let exercise = Profile.types[`${key}ExerciseName`]
            for (let garmin_exercise_id in exercise) {
                let exercise_key = exercise[garmin_exercise_id]
                exercise_key = this.toSnakeCase(exercise_key)
                let category_key = this.toSnakeCase(key)
                let en_name = this.exerciseTypes[`${category_key}_${exercise_key}`];
                let cn_name = this.exerciseTypesZhCN[`${category_key}_${exercise_key}`];
                let isBodyWeight = ExercisesJson.categories[category_key]?.exercises[exercise_key]?.isBodyWeight ?? false
                //category_id  garmin_exercise_id exercise_key 
                let values = [category_id, garmin_exercise_id, `${category_key}_${exercise_key}`, en_name, cn_name, isBodyWeight]
                data.push(values);
            }
        }
        return data;
    }

    async compareExerciseData(dataList) {
        let newData = [];
        let updateData = [];
        //无改动数据
        let noChangeData = [];
        let sql = `SELECT key,en_name,cn_name,is_body_weight FROM sys_exercise where exercise_category_id=$1 and garmin_exercise_id=$2`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, [values[0], values[1]]);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
                continue;
            }
            if (dbData[0].key != values[2] || dbData[0].en_name != values[3] || dbData[0].cn_name != values[4] || dbData[0].is_body_weight != values[5]) {
                updateData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, updateData, noChangeData };
    }

    async updateExerciseData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_exercise(exercise_category_id, garmin_exercise_id,key,en_name,cn_name,is_body_weight) VALUES($1,$2,$3,$4,$5,$6)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }

        let updateData = compareData.updateData;
        //更新数据sql
        sql = `UPDATE sys_exercise SET  key=$3,en_name=$4,cn_name=$5,is_body_weight=$6 WHERE exercise_category_id=$1 and garmin_exercise_id=$2`;
        for (let i = 0; i < updateData.length; i++) {
            let values = updateData[i];
            await client.query(sql, values);
        }
    }


    async parseExerciseToMusclesJson(jsonData, map) {
        let data = [];
        for (let category_key in jsonData.categories) {
            let category = jsonData.categories[category_key];
            for (let exercise_key in category.exercises) {
                let exercise = category.exercises[exercise_key];

                let exercise_id = await this.getExerciseIdByKey(`${category_key}_${exercise_key}`);
                if (exercise_id == undefined) {
                    continue;
                }
                let primaryMuscles = exercise.primaryMuscles ?? []
                for (let muscleKey of primaryMuscles) {
                    let muscle_id = await this.getMuscleIdByKey(muscleKey);
                    if (muscle_id == undefined) {
                        continue
                    }
                    if (map[`${exercise_id}_${muscle_id}_1`]) {
                        continue
                    }
                    map[`${exercise_id}_${muscle_id}_1`] = true;
                    data.push([exercise_id, muscle_id, 1]);
                }
                let secondaryMuscles = exercise.secondaryMuscles ?? []
                for (let muscleKey of secondaryMuscles) {
                    let muscle_id = await this.getMuscleIdByKey(muscleKey);
                    if (muscle_id == undefined) {
                        continue
                    }
                    if (map[`${exercise_id}_${muscle_id}_2`]) {
                        continue
                    }
                    map[`${exercise_id}_${muscle_id}_2`] = true;
                    data.push([exercise_id, muscle_id, 2]);
                }
            }
        }

        return data;
    }
    toCamelCase(str) {
        return str.replace(/\_(\w)/g, (match, letter) => letter.toUpperCase());
    }
    //验证数据是否是全的
    async checkExerciseJsonData(jsonData) {
        for (let category_key in jsonData.categories) {
            let category = jsonData.categories[category_key];

            for (let exercise_key in category.exercises) {
                //检查类别是否存在
                let exerciseCategory = Profile.types.exerciseCategory
                let exercise_category_keys = Object.values(exerciseCategory)
                    .map(key => {
                        return this.toSnakeCase(key)
                    })
                if (!exercise_category_keys.includes(category_key)) {
                    console.log(`Profile中类别 ${category_key} 不存在`);
                }
                //exercise_key转驼峰命名法
                let key = this.toCamelCase(category_key.toLowerCase());
                //检查具体类
                let exercise = Profile.types[`${key}ExerciseName`]
                if (!exercise) {
                    console.log(`Profile中类别 ${category_key}_${exercise_key} 不存在`);
                }
            }
        }
    }
    async parseExerciseToMusclesData() {
        // let data = [];
        let map = {};
        let data1 = await this.parseExerciseToMusclesJson(ExercisesJson, map);
        let data2 = await this.parseExerciseToMusclesJson(PilatesJson, map);
        let data3 = await this.parseExerciseToMusclesJson(YogaJson, map);
        let data4 = await this.parseExerciseToMusclesJson(MobilityJson, map);

        // data1.forEach(item => {
        //     if (data.find(item1 => item1[0] == item[0] && item1[1] == item[1] && item1[2] == item[2])) {
        //         repitData.push(item)
        //         return
        //     }
        //     data.push(item);
        // });
        // data2.forEach(item => {
        //     if (data.find(item1 => item1[0] == item[0] && item1[1] == item[1] && item1[2] == item[2])) {
        //         repitData.push(item)
        //         return
        //     }
        //     data.push(item);
        // });
        // console.log("重复数据", map);
        return data1.concat(data2).concat(data3).concat(data4);
    }

    async getMuscleIdByKey(key) {
        let sql = `SELECT id FROM sys_muscles where key=$1`;
        let result = await client.query(sql, [key]);
        return result.rows[0]?.id;
    }

    async getExerciseIdByKey(key) {
        let sql = `SELECT id FROM sys_exercise where key=$1`;
        let result = await client.query(sql, [key]);
        return result.rows[0]?.id;
    }

    async compareExerciseToMusclesData(dataList) {
        let newData = [];
        let noChangeData = [];
        let sql = `SELECT exercise_id,muscle_id,types FROM sys_exercise_to_muscle where exercise_id=$1 and muscle_id=$2 and types=$3`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, values);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, noChangeData };
    }

    async updateExerciseToMusclesData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_exercise_to_muscle(exercise_id,muscle_id,types) VALUES($1,$2,$3)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }
    }
    async getEquipmentIdByKey(key) {
        let sql = `SELECT id FROM sys_equipment where key=$1`;
        let result = await client.query(sql, [key]);
        return result.rows[0]?.id;
    }
    async parseExerciseToEquipmentsData() {
        let data = []
        let map = {}
        for (let item of exerciseToEquipmentsJson) {
            //let item = exerciseToEquipmentsJson[idx];
            let category_key = item.exerciseCategoryKey;
            for (let exercise of item.exercisesInCategory) {
                let exercise_key = exercise.exerciseKey;
                for (let equipmentKey of exercise.equipmentKeys) {
                    let exercise_id = await this.getExerciseIdByKey(`${category_key}_${exercise_key}`)
                    if (exercise_id == undefined) {
                        continue
                    }
                    let equipment_id = await this.getEquipmentIdByKey(equipmentKey)
                    if (equipment_id == undefined) {
                        continue
                    }
                    //去重
                    if (map[`${exercise_id}_${equipment_id}`]) {
                        continue
                    }
                    data.push([exercise_id, equipment_id])
                    map[`${exercise_id}_${equipment_id}`] = true
                }
            }
        }
        return data
    }

    async compareExerciseToEquipmentsData(dataList) {
        let newData = [];
        let noChangeData = [];
        let sql = `SELECT * FROM sys_exercise_to_equipment where exercise_id=$1 and equipment_id=$2`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, values);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, noChangeData };
    }
    async updateExerciseToEquipmentData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_exercise_to_equipment(exercise_id,equipment_id) VALUES($1,$2)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }
    }

    async getCategoryIdByKey(key) {
        let sql = `SELECT id FROM sys_exercise_category WHERE key = $1`;
        let result = await client.query(sql, [key]);
        return result.rows[0]?.id;
    }

    async parseExerciseCategoryToMuscleData() {
        let data = [];
        let map = {};
        for (let category_key in ExercisesJson.categories) {
            let category = ExercisesJson.categories[category_key];
            let primaryMuscles = category.primaryMuscles ?? [];
            let secondaryMuscles = category.secondaryMuscles ?? [];
            if (primaryMuscles.length == 0 && secondaryMuscles.length == 0) {
                continue;
            }
            let category_id = await this.getCategoryIdByKey(category_key);
            if (category_id == undefined) {
                continue
            }
            for (let muscleKey of primaryMuscles) {
                let muscle_id = await this.getMuscleIdByKey(muscleKey);
                if (muscle_id == undefined) {
                    continue
                }
                if (map[`${category_id}_${muscle_id}_1`]) {
                    continue
                }
                data.push([category_id, muscle_id, 1])
            }
            for (let muscleKey of secondaryMuscles) {
                let muscle_id = await this.getMuscleIdByKey(muscleKey);
                if (muscle_id == undefined) {
                    continue
                }
                if (map[`${category_id}_${muscle_id}_2`]) {
                    continue
                }
                data.push([category_id, muscle_id, 2])
            }
        }
        return data;
    }

    async compareExerciseCategoryToMuscleData(dataList) {
        let newData = [];
        let noChangeData = [];
        let sql = `SELECT * FROM sys_exercise_category_to_muscle where exercise_category_id=$1 and muscle_id=$2 and muscle_role_type=$3`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, values);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, noChangeData };
    }

    async updateExerciseCategoryToMuscleData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_exercise_category_to_muscle(exercise_category_id,muscle_id,muscle_role_type) VALUES($1,$2,$3)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }
    }

    async parseExerciseToExerciseData() {
        let data = []
        let map = {}
        for (let category_key in ExercisesJson.categories) {
            let category = ExercisesJson.categories[category_key];
            for (let exercise_key in category.exercises) {
                let exercise = category.exercises[exercise_key];
                if (!exercise.counterpart) {
                    continue
                }
                let exercise_id = await this.getExerciseIdByKey(`${category_key}_${exercise_key}`)
                if (exercise_id == undefined) {
                    continue
                }
                let other_exercise_id = await this.getExerciseIdByKey(`${category_key}_${exercise.counterpart}`)
                if (other_exercise_id == undefined) {
                    continue
                }
                if (exercise_id == other_exercise_id) {
                    continue
                }
                if (map[`${exercise_id}_${other_exercise_id}`]) {
                    continue
                }
                map[`${exercise_id}_${other_exercise_id}`] = true;
                data.push([exercise_id, other_exercise_id])
            }
        }
        return data;
    }

    async compareExerciseToExerciseData(dataList) {
        let newData = [];
        let noChangeData = [];
        let sql = `SELECT * FROM sys_exercise_to_exercise where exercise_id=$1 and other_exercise_id=$2`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, values);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, noChangeData };
    }

    async updateExerciseToExerciseData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_exercise_to_exercise(exercise_id,other_exercise_id) VALUES($1,$2)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }
    }

    async parseFrontendCategoryExerciseRelationData(jsonData, frontendId) {
        let data = [];
        for (let category_key in jsonData.categories) {
            let category = jsonData.categories[category_key];
            for (let exercise_key in category.exercises) {
                let exercise_id = await this.getExerciseIdByKey(`${category_key}_${exercise_key}`)
                if (exercise_id == undefined) {
                    console.log(`${category_key}_${exercise_key}数据库中不存在`);
                    continue
                }
                data.push([frontendId, exercise_id])
            }
        }
        return data;
    }

    async parseFrontendCategoryExerciseData() {
        //Yoga.json ，Mobility.json ，Pilates.json
        let strength_training = await this.parseFrontendCategoryExerciseRelationData(ExercisesJson, 3);
        let cardio_training = await this.parseFrontendCategoryExerciseRelationData(ExercisesJson, 4);
        let hiit = await this.parseFrontendCategoryExerciseRelationData(ExercisesJson, 5);
        let Yoga = await this.parseFrontendCategoryExerciseRelationData(YogaJson, 6);
        let Pilates = await this.parseFrontendCategoryExerciseRelationData(PilatesJson, 7);
        let Mobility = await this.parseFrontendCategoryExerciseRelationData(MobilityJson, 8);
        return [...strength_training, ...cardio_training, ...hiit, ...Yoga, ...Pilates, ...Mobility];
    }

    async compareFrontendCategoryExerciseData(dataList) {
        let newData = [];
        let noChangeData = [];
        let sql = `SELECT * FROM sys_frontend_category_exercise_relations where frontend_category_id=$1 and sys_exercise_id=$2`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, values);
            if (result.rows.length == 0) {
                newData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, noChangeData };
    }

    async updateFrontendCategoryExerciseData(compareData) {
        let newData = compareData.newData;
        let sql = `INSERT INTO sys_frontend_category_exercise_relations(frontend_category_id,sys_exercise_id,sort_order) VALUES($1,$2,0)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }
    }


    parseSportData() {
        let data = []
        let sport = Profile.types.sport
        let subSport = Profile.types.subSport
        // id 是 //SportId*1000+SubSportId
        // id
        // sport_id
        // sport_name
        // sport_en_name
        // sport_cn_name
        // sub_sport_id
        // sub_sport_name
        // sub_sport_en_name
        // sub_sport_cn_name
        let map = {}
        for (let sport_id in sport) {
            let sport_key = sport[sport_id]
            let snake_sport_key = this.toSnakeCase(sport_key).toLowerCase()
            let sport_en_name = this.activity[`activity_type_${snake_sport_key}`];
            let sport_cn_name = this.activityZhCN[`activity_type_${snake_sport_key}`];
            console.log(sport_id, snake_sport_key, sport_en_name, sport_cn_name);
            for (let subSport_id in subSport) {
                let subsport_key = subSport[subSport_id]
                let snake_subsport_key = this.toSnakeCase(subsport_key).toLowerCase()

                let subsport_en_name = this.activity[`activity_type_${snake_subsport_key}`];
                if (!subsport_en_name) {
                    if (!snake_subsport_key.endsWith(snake_sport_key)) {
                        //没找到，就找加主类的
                        let snake_subsport_key2 = snake_subsport_key + '_' + snake_sport_key
                        subsport_en_name = this.activity[`activity_type_${snake_subsport_key2}`];
                    }
                }
                let subsport_cn_name = this.activityZhCN[`activity_type_${snake_subsport_key}`];
                if (!subsport_cn_name) {
                    if (!snake_subsport_key.endsWith(snake_sport_key)) {
                        //没找到，就找加主类的
                        let snake_subsport_key2 = snake_subsport_key + '_' + snake_sport_key
                        subsport_cn_name = this.activityZhCN[`activity_type_${snake_subsport_key2}`];
                    }
                }


                let id = parseInt(sport_id) * 1000 + parseInt(subSport_id)
                //  console.log(sport_id, subSport_id, id);
                if (map[id]) {
                    continue
                }
                data.push([id, sport_id, sport_key, sport_en_name, sport_cn_name,
                    subSport_id, subsport_key, subsport_en_name, subsport_cn_name])
                map[id] = true
            }
        }
        return data;
    }
    async compareSportData(dataList) {
        let newData = [];
        let updateData = [];
        //无改动数据
        let noChangeData = [];
        // id
        // sport_id
        // sport_name
        // sport_en_name
        // sport_cn_name
        // sub_sport_id
        // sub_sport_name
        // sub_sport_en_name
        // sub_sport_cn_name
        let sql = `SELECT id,sport_id,sport_name,sport_en_name,sport_cn_name,sub_sport_id,sub_sport_name,sub_sport_en_name,sub_sport_cn_name  FROM sys_sports where  id=$1`;
        for (let i = 0; i < dataList.length; i++) {
            let values = dataList[i];
            let result = await client.query(sql, [values[0]]);
            let dbData = result.rows;
            if (dbData.length == 0) {
                newData.push(values);
                continue;
            }
            //[id, sport_id, sport_key, sport_en_name, sport_cn_name, subSport_id, subsport_key, subsport_en_name, subsport_cn_name]
            if (dbData[0].sport_name != values[2] || dbData[0].sport_en_name != values[3]
                || dbData[0].sport_cn_name != values[4]
                || dbData[0].sub_sport_name != values[6] || dbData[0].sub_sport_en_name != values[7] || dbData[0].sub_sport_cn_name != values[8]) {
                updateData.push(values);
            } else {
                noChangeData.push(values);
            }
        }
        return { newData, updateData, noChangeData };
    }

    async updateSportData(compareData) {
        let newData = compareData.newData;
        // id
        // sport_id
        // sport_name
        // sport_en_name
        // sport_cn_name
        // sub_sport_id
        // sub_sport_name
        // sub_sport_en_name
        // sub_sport_cn_name
        //[id, sport_id, sport_name, sport_en_name, sport_cn_name, subSport_id, subsport_key, subsport_en_name, subsport_cn_name]
        let sql = `INSERT INTO sys_sports(id, sport_id,sport_name,sport_en_name,sport_cn_name,sub_sport_id,sub_sport_name,sub_sport_en_name,sub_sport_cn_name) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)`;
        for (let i = 0; i < newData.length; i++) {
            let values = newData[i];
            await client.query(sql, values);
        }

        let updateData = compareData.updateData;
        //更新数据sql
        sql = `UPDATE sys_sports SET  sport_id=$2,sport_name=$3,sport_en_name=$4,sport_cn_name=$5,sub_sport_id=$6,sub_sport_name=$7,sub_sport_en_name=$8,sub_sport_cn_name=$9 WHERE  id=$1 `;
        for (let i = 0; i < updateData.length; i++) {
            let values = updateData[i];
            await client.query(sql, values);
        }
    }
}
//切换模式
const schema = 'lianbei';

await client.query(`SET search_path TO ${schema}`)
    .then(() => {
        // 在这里执行查询以获取模式特定的数据
        // ...
        console.log(`已切换到${schema}模式并获取数据`);
    })
    .catch(err => console.log(`切换工作模式失败：${err}`));


let parseData = new ParseData();
let exerciseCategoryData = parseData.parseExerciseCategoryData();
//console.log("exerciseCategoryData：", exerciseCategoryData);
let compareData = await parseData.compareExerciseCategoryData(exerciseCategoryData);
console.log("ExerciseCategory新增数据：", compareData.newData);
console.log("ExerciseCategory更新数据：", compareData.updateData);
console.log("ExerciseCategory无改动数据：", compareData.noChangeData);
let musclesData = parseData.parseMusclesData();
let compareMuscleData = await parseData.compareMuscleData(musclesData);
console.log("Muscle新增数据：", compareMuscleData.newData);
console.log("Muscle更新数据：", compareMuscleData.updateData);
console.log("Muscle无改动数据：", compareMuscleData.noChangeData);
let equipmentData = parseData.parseEquipmentData();
let compareEquipmentData = await parseData.compareEquipmentData(equipmentData);
console.log("Equipment新增数据：", compareEquipmentData.newData);
console.log("Equipment更新数据：", compareEquipmentData.updateData);
console.log("Equipment无改动数据：", compareEquipmentData.noChangeData);
let exerciseData = parseData.parseExerciseData();
let compareExerciseData = await parseData.compareExerciseData(exerciseData);
console.log("Exercise新增数据：", compareExerciseData.newData);
console.log("Exercise更新数据：", compareExerciseData.updateData);
console.log("Exercise无改动数据：", compareExerciseData.noChangeData);
let sportData = parseData.parseSportData();
let compareSportData = await parseData.compareSportData(sportData);
console.log("Sport新增数据：", compareSportData.newData);
console.log("Sport更新数据：", compareSportData.updateData);
console.log("Sport无改动数据：", compareSportData.noChangeData);
//基础表先入库
//等待用户从控制台输入y 或 n
let updateBaseData = await new Promise((resolve, reject) => {
    if (compareData.newData.length == 0 && compareData.updateData.length == 0 &&
        compareMuscleData.newData.length == 0 && compareMuscleData.updateData.length == 0 &&
        compareEquipmentData.newData.length == 0 && compareEquipmentData.updateData.length == 0 &&
        compareExerciseData.newData.length == 0 && compareExerciseData.updateData.length == 0 &&
        compareSportData.newData.length == 0 && compareSportData.updateData.length == 0
    ) {
        console.log('没有需要更新的基础数据！');
        resolve(false);
        return
    }
    process.stdin.resume();
    process.stdin.setEncoding('utf8');
    process.stdout.write('是否更新数据库？(y/n)');
    process.stdin.on('data', async (chunk) => {
        let input = chunk.trim().toLowerCase();
        if (input == 'y') {
            resolve(true);
        } else {
            resolve(false);
        }
    })
})
if (updateBaseData) {
    console.log("正在更新数据库");
    await parseData.updateExerciseCategoryData(compareData);
    await parseData.updateMuscleData(compareMuscleData);
    await parseData.updateEquipmentData(compareEquipmentData);
    await parseData.updateExerciseData(compareExerciseData);
    await parseData.updateSportData(compareSportData);
    console.log("数据库更新成功");
}

//然后是关系表

let exerciseToMusclesData = await parseData.parseExerciseToMusclesData();
let compareExerciseToMusclesData = await parseData.compareExerciseToMusclesData(exerciseToMusclesData);
console.log("ExerciseToMuscles新增数据：", compareExerciseToMusclesData.newData);
console.log("ExerciseToMuscles无改动数据：", compareExerciseToMusclesData.noChangeData);
let exerciseToEquipmentsData = await parseData.parseExerciseToEquipmentsData();
let compareExerciseToEquipmentsData = await parseData.compareExerciseToEquipmentsData(exerciseToEquipmentsData);
console.log("ExerciseToEquipments新增数据：", compareExerciseToEquipmentsData.newData);
console.log("ExerciseToEquipments无改动数据：", compareExerciseToEquipmentsData.noChangeData);
let exerciseCategoryToMuscleData = await parseData.parseExerciseCategoryToMuscleData();
let compareExerciseCategoryToMuscleData = await parseData.compareExerciseCategoryToMuscleData(exerciseCategoryToMuscleData);
console.log("ExerciseCategoryToMuscle新增数据：", compareExerciseCategoryToMuscleData.newData);
console.log("ExerciseCategoryToMuscle无改动数据：", compareExerciseCategoryToMuscleData.noChangeData);
let exerciseToExerciseData = await parseData.parseExerciseToExerciseData();
let compareExerciseToExerciseData = await parseData.compareExerciseToExerciseData(exerciseToExerciseData);
console.log("ExerciseToExerciseData新增数据：", compareExerciseToExerciseData.newData);
console.log("ExerciseToExerciseData无改动数据：", compareExerciseToExerciseData.noChangeData);
//解析前台类目和运动对应关系frontend_category_exercise_relations
let frontend_category_exercise = await parseData.parseFrontendCategoryExerciseData();
let compareFrontendCategoryExerciseData = await parseData.compareFrontendCategoryExerciseData(frontend_category_exercise);
console.log("frontend_category_exercise新增数据：", compareFrontendCategoryExerciseData.newData);
console.log("frontend_category_exercise无改动数据：", compareFrontendCategoryExerciseData.noChangeData);
//检查数据
await parseData.checkExerciseJsonData(ExercisesJson);
await parseData.checkExerciseJsonData(PilatesJson);
await parseData.checkExerciseJsonData(YogaJson);
await parseData.checkExerciseJsonData(MobilityJson);

//等待用户从控制台输入y 或 n
let update = await new Promise((resolve, reject) => {
    if (
        compareExerciseToMusclesData.newData.length == 0 &&
        compareExerciseToEquipmentsData.newData.length == 0 &&
        compareExerciseCategoryToMuscleData.newData.length == 0 &&
        compareExerciseToExerciseData.newData.length == 0 &&
        compareFrontendCategoryExerciseData.newData.length == 0
    ) {
        console.log('没有需要更新的数据！');
        resolve(false);
        return
    }
    process.stdin.resume();
    process.stdin.setEncoding('utf8');
    process.stdout.write('是否更新数据库？(y/n)');
    process.stdin.on('data', async (chunk) => {
        let input = chunk.trim().toLowerCase();
        if (input == 'y') {
            resolve(true);
        } else {
            resolve(false);
        }
    })
})
//如果更新数据库
if (update) {
    //更新数据中
    console.log("正在更新数据库");

    await parseData.updateExerciseToMusclesData(compareExerciseToMusclesData);
    await parseData.updateExerciseToEquipmentData(compareExerciseToEquipmentsData);
    await parseData.updateExerciseCategoryToMuscleData(compareExerciseCategoryToMuscleData);
    await parseData.updateExerciseToExerciseData(compareExerciseToExerciseData);
    await parseData.updateFrontendCategoryExerciseData(compareFrontendCategoryExerciseData);
    console.log("数据库更新成功");
}
console.log("正在断开数据库连接");
//数据库断开连接
client.end();